﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using Com.DAO;
using Com.Data;

namespace WF.Common
{
    /// <summary>
    /// DataAccess 的摘要说明。

    /// </summary>
    public class DataAccess
    {
        #region 变量定义
        private ArrayList checkList = new ArrayList();//需检查的字段集合
        private ArrayList searchList = new ArrayList();//需作为普通查询条件的字段集合
        private System.Collections.SortedList moreSearchList = new SortedList();//需进行多项条件查询的字段集合

        private ArrayList sortList = new ArrayList();//需要排序的字段集合
        private ArrayList spParams = new ArrayList();//存储过程所需参数集合
        private ArrayList columnList = new ArrayList();
        private string searchOp = "and";//拼凑查询语句的默认操作运算符
        private Com.DAO.DataManager dataManager = null;
        private string spName = "";//存储过程名称或表名

        #endregion

        #region 构造函数


        public DataAccess()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }

        public DataAccess(Com.DAO.DataManager p_DataManager)
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
            this.dataManager = p_DataManager;
        }

        public DataAccess(string storedProcedureName, Com.DAO.DataManager p_DataManager)
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
            this.spName = storedProcedureName;
            this.dataManager = p_DataManager;
        }

        #endregion

        #region 属性定义


        public string SearchType
        {
            set
            {
                this.searchOp = value;
            }
            get
            {
                return this.searchOp;
            }
        }

        public string StoredProcedureName
        {
            set
            {
                this.spName = value;
            }
            get
            {
                return this.spName;
            }
        }

        #endregion

        #region 方法定义

        /// <summary>
        /// 检查输入的字符窜是否是合法的数字

        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        private bool CheckNum(string str)
        {
            try
            {
                System.Convert.ToDecimal(str);
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        /// <summary>
        /// 检查输入的字符窜是否是合法的日期型
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        private bool CheckDate(string str)
        {
            try
            {
                System.DateTime d = System.DateTime.Parse(str);
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        /// <summary>
        /// 设置存储过程参数
        /// </summary>
        /// <param name="paramName">参数名</param>
        /// <param name="paramValue">参数值</param>
        public void SetSpParam(string paramName, string paramValue)
        {
            ArrayList key = new ArrayList();
            key.Add(paramName);
            key.Add(paramName);
            this.spParams.Add(key);
        }

        /// <summary>
        /// 多项条件查询, 条件为或
        /// </summary>
        /// <param name="colName">字段名</param>
        /// <param name="colValue">字段值</param>
        public void SetSearch(string colName, string colValue)
        {
            ArrayList v = new ArrayList();
            if (this.moreSearchList.ContainsKey(colName))
            {
                v = (ArrayList)this.moreSearchList[colName];
                v.Add(colValue);
                this.moreSearchList.SetByIndex(this.moreSearchList.IndexOfKey(colName), v);

            }
            else
            {
                v.Add(colValue);
                this.moreSearchList.Add(colName, v);
            }
        }

        /// <summary>
        /// 设置查询字段，colValue2不为空时，则为区间查询。

        /// 此时sqlOperator='[]'表示colValue1=<colName<=colValue2
        /// sqlOperator='<>'表示colValue1<colName<colValue2
        /// </summary>
        /// <param name="colName">字段名</param>
        /// <param name="colValue1">字段值1</param>
        /// <param name="colValue2">字段值2</param>
        /// <param name="sqlOperator">运算符，单运算符=、>、<、>=、=<、like；区间运算符[]、<>、[>、<]</param>
        public void SetSearch(string colName, string colValue1, string colValue2, string sqlOperator)
        {
            ArrayList list = new ArrayList();
            list.Add(colName);
            list.Add(colValue1);
            list.Add(colValue2);
            list.Add(sqlOperator);
            this.searchList.Add(list);
        }

        /// <summary>
        /// 设置查询字段
        /// </summary>
        /// <param name="colName">字段名</param>
        /// <param name="colValue">字段值</param>
        /// <param name="sqlOperator">运算符，单运算符=、>、<、>=、=<、like</param>
        public void SetSearch(string colName, string colValue, string sqlOperator)
        {
            SetSearch(colName, colValue, "", sqlOperator);
        }

        /// <summary>
        /// 设置查询字段
        /// </summary>
        /// <param name="colName">字段名</param>
        /// <param name="colValue">字段值</param>
        /// <param name="sqlOperator">运算符</param>
        /// <param name="isIgnoreBlankString">是否忽略空字符串的字段值</param>
        public void SetSearch(string colName, string colValue, string sqlOperator, bool isIgnoreBlankString)
        {
            if (isIgnoreBlankString)
            {
                if (colValue != null && colValue != "")
                {
                    if (sqlOperator.ToUpper() == "LIKE")
                    {
                        this.SetSearch(colName, "%" + colValue + "%", sqlOperator);
                    }
                    else
                    {
                        this.SetSearch(colName, colValue, sqlOperator);
                    }
                }
            }
            else
            {
                this.SetSearch(colName, colValue, sqlOperator);
            }
        }

        /// <summary>
        /// 设置排序参数
        /// </summary>
        /// <param name="sortName">字段名称</param>
        /// <param name="sortType">排序方式:desc倒序，为空正序</param>
        public void SetSort(string sortName, string sortType)
        {
            ArrayList key = new ArrayList();
            key.Add(sortName);
            key.Add(sortType);
            this.sortList.Add(key);
        }

        /// <summary>
        /// 设置字段名及字段别名, columnName 字段名称 , showName 字段别名
        /// </summary>
        /// <param name="columnName"></param>
        /// <param name="showName"></param>
        public void SetColumnName(string columnName, string showName)
        {
            ArrayList col = new ArrayList();
            col.Add(columnName);
            col.Add(showName);
            this.columnList.Add(col);
        }

        /// <summary>
        /// 设置需要检查的字段
        /// </summary>
        /// <param name="colName">字段名称</param>
        /// <param name="colShowName">显示名称</param>
        /// <param name="repeat">是否检查重复</param>
        /// <param name="num">是否检查为数字</param>
        /// <param name="date">是否检查为日期</param>
        /// <param name="nullvalue">是否检查为空</param>
        public void SetCheck(string colName, string colShowName, bool repeat, bool num, bool date, bool nullValue)
        {
            ArrayList col = new ArrayList();
            col.Add(colName);
            col.Add(colShowName);
            col.Add(repeat);
            col.Add(num);
            col.Add(date);
            col.Add(nullValue);
            this.checkList.Add(col);
        }

        /// <summary>
        /// 生成查询条件
        /// </summary>
        /// <returns></returns>
        public string SearchQuery()
        {
            string rtn = "";
            for (int i = 0; i < this.searchList.Count; i++)
            {
                ArrayList list = (ArrayList)this.searchList[i];
                string colName = list[0].ToString().Trim();
                string colValue1 = list[1].ToString().Trim();
                string colValue2 = list[2].ToString().Trim();
                string sqlOperator = list[3].ToString().Trim();
                colName = this.FormatColumnName(colName);

                if (colName != "" && colValue1 != "" && colValue2 != "" && sqlOperator != "")
                {
                    rtn += " " + InterzoneQuery(colName, colValue1, colValue2, sqlOperator);
                }
                else if (colName != "" && colValue1 != "" && colValue2 == "")
                {
                    if (sqlOperator.Trim().ToLower() == "like")
                    {
                        rtn += " " + this.searchOp.Trim() + colName + "Like '%" + colValue1 + "%' ";
                    }
                    else
                    {
                        rtn += " " + this.searchOp.Trim() + colName + sqlOperator + " '" + colValue1 + "' ";
                    }

                }
            }

            rtn = rtn + MoreSearchQuery() + " ";
            if (rtn.Trim().Length != 0)
                rtn = " where " + rtn.Substring(5);

            string sort = SortQuery();
            if (sort != "")
            {
                rtn = rtn + " ORDER BY " + sort + " ";
            }


            return rtn;
        }

        /// <summary>
        /// 生成区间查询字符窜

        /// </summary>
        /// <param name="colName">字段名</param>
        /// <param name="colValue1">字段值1</param>
        /// <param name="colValue2">字段值2</param>
        /// <param name="sqlOperator">运算符，区间运算符[]、<>、[>、<]</param>
        /// <returns></returns>
        private string InterzoneQuery(string colName, string colValue1, string colValue2, string sqlOperator)
        {
            string rtn = "";

            switch (sqlOperator)
            {
                case "[]":
                    rtn += this.searchOp.Trim() + colName + ">= '" + colValue1 + "' and" + colName + "<= '" + colValue2 + "' ";
                    break;
                case "<>":
                    rtn += this.searchOp.Trim() + colName + "> '" + colValue1 + "' and" + colName + "< '" + colValue2 + "' ";
                    break;
                case "<]":
                    rtn += this.searchOp.Trim() + colName + "> '" + colValue1 + "' and" + colName + "<= '" + colValue2 + "' ";
                    break;
                case "[>":
                    rtn += this.searchOp.Trim() + colName + ">= '" + colValue1 + "' and" + colName + "< '" + colValue2 + "' ";
                    break;
            }
            return rtn;
        }

        /// <summary>
        /// 生成多项条件查询语句
        /// </summary>
        /// <returns></returns>
        private string MoreSearchQuery()
        {
            string rtn = "";
            if (this.moreSearchList.Count > 0)
            {
                System.Collections.IList keys = this.moreSearchList.GetKeyList();

                string str = "";
                string col = "";
                for (int k = 0; k < keys.Count; k++)
                {
                    col = keys[k].ToString();
                    ArrayList values = (ArrayList)this.moreSearchList[col];
                    col = this.FormatColumnName(col);
                    int co = 0;
                    for (int v = 0; v < values.Count; v++)
                    {
                        if (co == 0)
                        {
                            str = "'" + values[v].ToString() + "'";
                            co = 1;
                        }
                        else
                        {
                            str += ",'" + values[v].ToString() + "'";
                        }
                    }
                    rtn += " AND" + col + "IN (" + str + ") ";
                }
            }
            return rtn;
        }

        /// <summary>
        /// 生成SQL排序语句
        /// </summary>
        /// <returns></returns>
        private string SortQuery()
        {
            string rtn = "";
            for (int i = 0; i < this.sortList.Count; i++)
            {
                ArrayList key = (ArrayList)this.sortList[i];
                string sortName = key[0].ToString().Trim();
                string sortType = key[1].ToString().Trim();
                if (sortName != "")
                {
                    sortName = this.FormatColumnName(sortName);
                    if (rtn == "")
                    {
                        rtn = sortName + " " + sortType;
                    }
                    else
                    {
                        rtn = rtn + " , " + sortName + " " + sortType;
                    }
                }
            }
            return rtn;
        }

        /// <summary>
        /// 返回设置字段的sql语句， 与 setColumnName 方法 配用
        /// </summary>
        /// <returns></returns>
        public string ColumnQuery()
        {
            string rtn = "";
            if (this.columnList.Count > 0)
            {
                for (int i = 0; i < this.columnList.Count; i++)
                {
                    ArrayList list = (ArrayList)this.columnList[i];
                    string columnName = list[0].ToString().Trim();
                    string showName = list[1].ToString().Trim();
                    columnName = FormatColumnName(columnName);

                    if (i == 0)
                    {
                        if (showName.Length > 0)
                        {
                            rtn = columnName + "AS " + showName + " ";
                        }
                        else
                        {
                            rtn = columnName;
                        }
                    }
                    else
                    {
                        if (showName.Length > 0)
                        {
                            rtn = rtn + "," + columnName + "AS " + showName + " ";
                        }
                        else
                        {
                            rtn = rtn + "," + columnName;
                        }
                    }
                }
            }
            else
            {
                rtn = " * ";
            }
            return rtn;
        }

        private string FormatColumnName(string columnName)
        {
            string rtn = "";
            if (columnName != "")
            {
                if (columnName.IndexOf(".") > 0)
                {
                    string[] arrColName = columnName.Split('.');
                    rtn = " [" + arrColName[0] + "].[" + arrColName[1] + "] ";
                }
                else
                {
                    rtn = " [" + columnName + "] ";
                }
            }

            return rtn;
        }

        /// <summary>
        /// 通过SQL查询语句获得数据集

        /// </summary>
        /// <param name="strSqlText">SQL查询语句</param>
        /// <returns></returns>
        public DataSet GetDataSet(string strSqlText)
        {
            try
            {
                DataSet ds = new DataSet();
                SqlDataAdapter com = new SqlDataAdapter(strSqlText, this.dataManager.Connection());
                com.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 获得指定表的记录集

        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public DataSet GetDataSet()
        {
            try
            {
                string strSqlText = "";
                strSqlText += "select " + this.ColumnQuery() + " from " + this.spName + this.SearchQuery();
                return this.GetDataSet(strSqlText);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 获得指定表的记录集

        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public DataSet GetTableData(string tableName)
        {
            try
            {
                string strSqlText = "";
                strSqlText += "select " + this.ColumnQuery() + " from " + tableName + this.SearchQuery();
                return this.GetDataSet(strSqlText);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 根据指定的存储过程获取数据集
        /// </summary>
        /// <param name="storedProcedureName">存储过程名称</param>
        /// <returns></returns>
        public DataSet GetDataSetBySP(string storedProcedureName)
        {
            try
            {
                System.Data.SqlClient.SqlCommand cmd =
                    new System.Data.SqlClient.SqlCommand(storedProcedureName, this.dataManager.Connection());
                cmd.CommandType = System.Data.CommandType.StoredProcedure;

                for (int i = 0; i < this.spParams.Count; i++)
                {
                    ArrayList param = (ArrayList)this.spParams[i];
                    if (param != null)
                    {
                        cmd.Parameters.AddWithValue(param[0].ToString(), param[1].ToString());
                    }
                }

                System.Data.SqlClient.SqlDataAdapter com = new System.Data.SqlClient.SqlDataAdapter(cmd);
                System.Data.DataSet ds = new System.Data.DataSet();
                com.Fill(ds);


                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 根据存储过程获取数据集

        /// </summary>
        /// <returns></returns>
        public DataSet GetDataSetBySP()
        {
            return GetDataSetBySP(this.spName);
        }

        public string ToSQL()
        {
            try
            {
                string strSqlText = "";
                strSqlText += "select " + this.ColumnQuery() + " from " + this.spName + this.SearchQuery();
                return strSqlText;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion
    }
}
